--for mssql2000
--create tables


CREATE TABLE user_role (
       user_id              varchar(50) NOT NULL,
       role_id              varchar(50) NOT NULL
)
go

CREATE TABLE [user] (
       id                   varchar(50) NOT NULL,
       name                 varchar(100) NOT NULL,
       description          varchar(200) NULL,
       password             varchar(20) NULL,
       auth_type            varchar(50) NOT NULL
)
go


ALTER TABLE [user]
       ADD PRIMARY KEY (id ASC)
go


CREATE TABLE role (
       id                   varchar(50) NOT NULL,
       name                 varchar(100) NOT NULL,
       description          varchar(200) NULL
)
go


ALTER TABLE role
       ADD PRIMARY KEY (id ASC)
go


CREATE TABLE plugins (
       id                   varchar(50) NOT NULL,
       name                 varchar(50) NOT NULL,
       data                 image NULL,
       [desc]                 varchar(200) NULL,
       version              varchar(30) NULL
)
go


ALTER TABLE plugins
       ADD PRIMARY KEY (id ASC)
go


CREATE TABLE module (
       id                   varchar(50) NOT NULL,
       name                 varchar(50) NOT NULL,
       plugin_id            varchar(50) NULL,
       param                varchar(2000) NULL,
       init_string          varchar(2000) NULL
)
go


ALTER TABLE module
       ADD PRIMARY KEY (id ASC)
go


CREATE TABLE [log] (
       id                   varchar(50) NOT NULL,
       datetime             datetime NOT NULL,
       computer_name        nvarchar(50) NULL,
       [user_id]              nvarchar(50) NOT NULL,
       source               nvarchar(200) NOT NULL,
       type                 int NULL,
       category             nvarchar(50) NULL,
       action               nvarchar(50) NOT NULL,
       comment              nvarchar(500) NULL
)
go


ALTER TABLE [log]
       ADD PRIMARY KEY (id ASC)
go


CREATE TABLE globalvariables (
       [key]                  varchar(200) NOT NULL,
       value                text NULL
)
go


ALTER TABLE globalvariables
       ADD PRIMARY KEY ([key] ASC)
go


CREATE TABLE function_type (
       id                   varchar(50) NOT NULL,
       name                 varchar(50) NULL
)
go


ALTER TABLE function_type
       ADD PRIMARY KEY (id ASC)
go


DROP TABLE function_module
go

CREATE TABLE function_module (
       function_id          varchar(50) NOT NULL,
       module_id            varchar(50) NOT NULL
)
go


ALTER TABLE function_module
       ADD PRIMARY KEY (function_id ASC)
go


CREATE TABLE function_authorization (
       id                   varchar(50) NOT NULL,
       function_id          varchar(50) NULL,
       role_id              varchar(50) NULL
)
go


ALTER TABLE function_authorization
       ADD PRIMARY KEY (id ASC)
go


CREATE TABLE [function] (
       id                   varchar(50) NOT NULL,
       name                 varchar(500) NOT NULL,
       type_id              varchar(50) NULL,
       parent_id            varchar(50) NULL
)
go


ALTER TABLE [function]
       ADD PRIMARY KEY (id ASC)
go


CREATE TABLE clientconfig (
       id                   char(50) NOT NULL,
       host                 char(30) NOT NULL,
       module               char(100) NOT NULL,
       content              ntext NULL
)
go

CREATE UNIQUE INDEX IX_clientconfig2 ON clientconfig
(
       host                           ASC,
       module                         ASC
)
go


ALTER TABLE clientconfig
       ADD PRIMARY KEY (id ASC)
go


